-- 使用 Postgresql 展示資料庫集合操作
-- 在 .psqlrc 中設定 \pset null '¤'
-- 將 NULL 顯示為  ¤ , 以利觀察

create table it191210a (
  id int generated always as identity
, txt text 
);

create table it191210b (
  id int generated always as identity
, txt text 
);

create table it191210c (
  id int generated always as identity
, txt text 
);

insert into it191210a (txt) values
('小島南'),('小島南'),('小島南'),
('初川南'),('初川南'),('初川南'),
('相沢南'),
('明里つむぎ'),('明里つむぎ'),
('七沢みあ'),
(NULL);

insert into it191210b (txt) values
('小島南'),('小島南'),
('相沢南'),('相沢南'),('相沢南'),('相沢南'),
('明里つむぎ');

-- it191210c 不輸入資料.
----
-- UNION [DISTINCT] 

select txt
  from it191210a
union
select txt
  from it191210b;

+------------+
|    txt     |
+------------+
| ¤          |
| 明里つむぎ |
| 初川南     |
| 小島南     |
| 七沢みあ   |
| 相沢南     |
+------------+
(6 rows)

-- 注意,包含了 NULL

-- UNION ALL
select txt
     , array_agg(q_id) query_and_id
  from (select 'a_' || id::text as q_id
             , txt
          from it191210a
         union all
         select 'b_' || id::text as q_id
              , txt
           from it191210b) c
 group by txt;

+------------+-----------------------+
|    txt     |     query_and_id      |
+------------+-----------------------+
| ¤          | {a_11,b_8}            |
| 七沢みあ   | {a_10}                |
| 小島南     | {a_1,a_2,a_3,b_1,b_2} |
| 明里つむぎ | {a_8,a_9,b_7}         |
| 相沢南     | {a_7,b_3,b_4,b_5,b_6} |
| 初川南     | {a_4,a_5,a_6}         |
+------------+-----------------------+
(6 rows)

-- 為了不顯示一長串,利用了 array 功能.

--------------
--  EXCEPT [DISTINCT] 

-- query a except query b
select txt
  from it191210a
except
select txt
  from it191210b;
  
+----------+
|   txt    |
+----------+
| 七沢みあ |
| 初川南   |
+----------+
(2 rows)

-- query b except query a
select txt
  from it191210b
except
select txt
  from it191210a;

+-----+
| txt |
+-----+
+-----+
(0 rows)


-- 相同的做 except

select txt
  from it191210a
except 
select txt
  from it191210a;

+-----+
| txt |
+-----+
+-----+
(0 rows)
---------------
-- EXCEPT ALL

-- query a except all query b
select txt
  from it191210a
except all
select txt
  from it191210b;

+------------+
|    txt     |
+------------+
| 七沢みあ   |
| 小島南     |
| 明里つむぎ |
| 初川南     |
| 初川南     |
| 初川南     |
+------------+
(6 rows)

-- query b except all query a
select txt
  from it191210b
except all
select txt
  from it191210a;

+--------+
|  txt   |
+--------+
| 相沢南 |
| 相沢南 |
| 相沢南 |
+--------+
(3 rows)
---------------
-- intersect [distinct]
select txt
  from it191210a
intersect
select txt
  from it191210b;

+------------+
|    txt     |
+------------+
| ¤          |
| 小島南     |
| 明里つむぎ |
| 相沢南     |
+------------+
(4 rows)

-- 注意到 包含了 NULL

-- intersect all
select txt
  from it191210a
intersect all
select txt
  from it191210b;

+------------+
|    txt     |
+------------+
| ¤          |
| 小島南     |
| 小島南     |
| 明里つむぎ |
| 相沢南     |
+------------+
(5 rows)

--------------
-- 與空集合運算 
-- query a union / except query c (沒資料, 空集合)

select txt
  from it191210a
union
select txt
  from it191210c;
  
+------------+
|    txt     |
+------------+
| ¤          |
| 明里つむぎ |
| 初川南     |
| 小島南     |
| 七沢みあ   |
| 相沢南     |
+------------+
(6 rows)

select txt
  from it191210a
except
select txt
  from it191210c;

+------------+
|    txt     |
+------------+
| ¤          |
| 七沢みあ   |
| 小島南     |
| 明里つむぎ |
| 相沢南     |
| 初川南     |
+------------+
(6 rows)

-- 相當於 query a 做 distinct
select distinct txt
  from it191210a;
+------------+
|    txt     |
+------------+
| ¤          |
| 七沢みあ   |
| 小島南     |
| 明里つむぎ |
| 相沢南     |
| 初川南     |
+------------+
(6 rows)

-- all 的情況 就跟 query a 一樣,可以自行驗證.